package com.bsh.repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.bsh.mybatis.IRepository.UserMapper;
import com.bsh.mybatis.dataView.UserInfoExt;
import com.bsh.mybatis.model.User;
import com.bsh.utils.JdbcHelper;
import com.bsh.utils.Pager;

@Repository
public class DaoUser {
	@Resource
	SqlSessionTemplate session;
	@Resource
	JdbcTemplate jdbcTemplate;
	@Resource
	NamedParameterJdbcTemplate namedJdbcTemplate;
	@Resource
	JdbcHelper jh;

	UserMapper getMapper() {
		return session.getMapper(UserMapper.class);
	}

	public int deleteByPrimaryKey(Integer id) {
		int res = getMapper().deleteByPrimaryKey(id);
		return res;
	}

	public int insert(User record) {
		int res = getMapper().insert(record);
		return record.getId();
	}

	public int insertSelective(User record) {
		int res = getMapper().insertSelective(record);
		return record.getId();
	}

	public User selectByPrimaryKey(Integer id) {
		User row = getMapper().selectByPrimaryKey(id);
		return row;
	}

	public int updateByPrimaryKeySelective(User record) {
		int res = getMapper().updateByPrimaryKeySelective(record);
		return res;
	}

	public int updateByPrimaryKey(User record) {
		int res = getMapper().updateByPrimaryKey(record);
		return res;
	}

	/**
	 * 查询用户信息
	 * 
	 * @param id
	 * @return
	 */
	public UserInfoExt getUser(Integer id) {
		UserInfoExt row = getMapper().getUser(id);
		return row;
	}

	/**
	 * 添加用户
	 * 
	 * @param userid
	 * @return 新用户id
	 */
	public Integer addUser(User user) {
		UserMapper map = session.getMapper(UserMapper.class);
		map.insert(user);
		return user.getId();
	}

	/**
	 * 修改用户
	 * 
	 * @param userid
	 * @return
	 */
	public Integer editUser(User user) {
		UserMapper map = session.getMapper(UserMapper.class);
		return map.editUser(user);
	}

	/**
	 * 禁用用户
	 * 
	 * @param userid
	 * @return
	 */
	public Integer enableUser(int id, int isEnabele) {
		UserMapper map = session.getMapper(UserMapper.class);
		Integer res = map.enableUser(id, isEnabele);
		return res;
	}

	/**
	 * 分页查询
	 */
	public List<Map<String, Object>> getPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select u.* from user u where 1=1");
		if (!StringUtils.isEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		StringBuilder sb2 = new StringBuilder();
		MapSqlParameterSource ps2 = new MapSqlParameterSource();
		sb2.append("select u.*,(select name from user where id=u.pid) pname from user u where 1=1");
		if (!StringUtils.isEmpty(where)) {
			sb2.append(where);
		}
		sb2.append(" order by u.id desc limit :start,:size");
		ps2.addValue("start", pager.getStart());
		ps2.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb2.toString(), ps2);

		pager.setRowCount(count);
		return list;
	}

	/**
	 * 分页查询_导出
	 */
	public List<Map<String, Object>> getPageExport(String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps2 = new MapSqlParameterSource();
		sb.append("select u.*,(select name from user where id=u.pid) pname,");
		sb.append(" case when u.sex='F' then '女' when u.sex='M' then '男' else '' end sex_ch, ");
		sb.append(
				" case when u.is_merchant='-1' then '' when u.is_merchant='0' then '否' when u.is_merchant='1' then '是' else '' end is_merchant_ch, ");
		sb.append(
				" case when u.level='comm' then '普通' when u.level='vip' then 'VIP' when u.level='gold' then '金钻' else '' end level_ch, ");
		sb.append(" case when u.is_effect='1' then '有效' when u.is_effect='' then '无效' else '' end is_effect_ch,  ");
		sb.append(" DATE_FORMAT(u.create_time,'%Y-%m-%d %H:%i') create_time_ch");
		sb.append(" from user u where 1=1");
		if (!StringUtils.isEmpty(where)) {
			sb.append(where);
		}
		sb.append(" order by u.id desc");

		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps2);
		return list;
	}

	/**
	 * 取得登录用户信息
	 */
	public User getLoginUser(String userName, String password) {
		UserMapper dao = session.getMapper(UserMapper.class);
		User user = dao.getLoginUser(userName, password);
		return user;
	}

	/**
	 * 修改密码
	 */
	public int updatePassword(int id, String password, String newPassword) {
		UserMapper dao = session.getMapper(UserMapper.class);
		int res = dao.updatePassword(id, password, newPassword);
		return res;
	}

	/**
	 * 重置密码
	 */
	public int resetPassword(String mobile, String password, String smsVerifyCode) {
		UserMapper dao = session.getMapper(UserMapper.class);
		int res = dao.resetPassword(mobile, password, smsVerifyCode);
		return res;
	}

	/**
	 * 查用记by mobile
	 * 
	 * @param mobile
	 * @return
	 */
	public User selectByMobile(String mobile) {
		User row = getMapper().selectByMobile(mobile);
		return row;
	}

	/**
	 * 查用户by name
	 * 
	 * @param name
	 * @return
	 */
	public User selectByName(String name) {
		User row = getMapper().selectByName(name);
		return row;
	}

	/**
	 * 检查同时用户名和手机号
	 * 
	 * @param name
	 * @return
	 */
	public User selectByNameOrMobile(String name) {
		User row = getMapper().selectByNameOrMobile(name);
		return row;
	}

	/**
	 * 设置用户为商家
	 * 
	 * @param isMerchant
	 * @return
	 */
	public int setAsMerchant(int userid) {
		StringBuilder sb = new StringBuilder();
		sb.append("update user set is_merchant=1 where id=:userid");
		Map<String, Object> ps = new HashMap<String, Object>();
		ps.put("userid", userid);
		Integer res = jh.execute(sb.toString(), ps);
		return res;
	}

	/**
	 * 更新用户商家状态
	 * 
	 * @param isMerchant
	 * @return
	 */
	public int updateIsMerchant(int userid) {
		StringBuilder sb = new StringBuilder();
		sb = new StringBuilder();
		sb.append(
				"update user set is_merchant=(select id>0 from merchant where userid=:userid and status=1 LIMIT 1) where id=:userid");
		Map<String, Object> ps = new HashMap<String, Object>();
		ps.put("userid", userid);
		Integer res = jh.execute(sb.toString(), ps);
		return res;
	}

	/**
	 * 设置用户合伙人级别
	 * 
	 * @param userid
	 * @return
	 */
	public int setUserPartnerLevel(int userid, int partnerLevel) {
		UserMapper map = session.getMapper(UserMapper.class);
		return map.setUserPartnerLevel(userid, partnerLevel);
	}

	/**
	 * 查询出推荐用户列表
	 * 
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> getMyRecUserList(Pager pager, Integer userid) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append(" SELECT IFNULL(t1.head_img,'') as head_img,t1.mobile,IFNULL(t2.name,'') as name, ");
		sb.append(" CASE t1.level WHEN 'comm' THEN	'普通会员' WHEN 'vip' THEN 'VIP'	WHEN 'gold' THEN '金钻会员' END AS levelName ");
		sb.append(" FROM user t1 LEFT JOIN (SELECT DISTINCT userid,`name` FROM user_verify_info WHERE is_pass=1) t2 on t1.id=t2.userid WHERE (t1.is_merchant=-1 or t1.is_merchant is null) and t1.pid="+ userid);
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);
		sb.append(" order by t1.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		pager.setRowCount(count);
		return list;
	}

	/**
	 * 查询出推荐商户列表
	 * 
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> getMyRecMerchantList(Pager pager, Integer userid) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append(" SELECT t2.id,t2.merchant_name,rp.name as  provinceName,rc.name as cityName,ra.name as areaName,t2.address,t2.mobile,t2.img_cover_small ");
		sb.append(" FROM user t1 JOIN merchant t2 ON t1.id=t2.userid ");
		sb.append(" LEFT JOIN region rp ON rp.id=t2.province ");
		sb.append(" LEFT JOIN region rc ON rc.id=t2.city ");
		sb.append(" LEFT JOIN region ra ON ra.id=t2.area ");
		sb.append(" WHERE t1.is_merchant=1 AND  t1.pid=" + userid);
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);
		sb.append(" order by t2.create_time desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		pager.setRowCount(count);
		return list;
	}
    
	
	/**
	 * 获取微信关注列表
	 * 
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> getWxRecList(Pager pager, String  mobile) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append(" SELECT p_mobile,nickname,DATE_FORMAT(create_time,'%Y-%m-%d') as create_time from wx_recomend ");
		sb.append(" WHERE 1=1  AND  p_mobile=" + mobile);
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);
		sb.append(" order by create_time desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		pager.setRowCount(count);
		return list;
	}
		
	
	/**
     * 查询ip登录次数
     * @param ip
     * @return
     */
	public int getIpLoginTimes(String ip) {
		UserMapper map = session.getMapper(UserMapper.class);
		return map.selectIpLogin(ip);
	}
	
	/**
	 * 会员绑定银行卡分页查询
	 */
	public List<Map<String, Object>> getBankPage(Pager pager, String where) {
		StringBuilder sb2 = new StringBuilder();
		sb2.append("select u.*,(select name from user where id=u.pid) pname ");
		sb2.append(",b.bank_name,b.bank_card ");
		sb2.append("FROM user u ");
		sb2.append("join user_bank b on b.userid=u.id");		
		sb2.append(" where 1=1");
		if (!StringUtils.isEmpty(where)) {
			sb2.append(where);
		}
		MapSqlParameterSource ps2 = new MapSqlParameterSource();
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb2.toString() + ")v", ps2);
		

		sb2.append(" order by u.id desc limit :start,:size");
		ps2.addValue("start", pager.getStart());
		ps2.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb2.toString(), ps2);

		pager.setRowCount(count);
		return list;
	}	
	
	

}